/**
 * Bookshelf数据模型模块
 * model\sqlite\bookshelf.js
 */

//导入SQLite3模块
const sqlite3 = require("sqlite3").verbose();
//导入工具模块
const util = require("../../common/util");
//导入配置模块
const config = require("../../common/config");

class BookshelfDB {
  /**
   * @constructor
   * @private
   */
  constructor() {
    this.dbFile = config.dbFile;
    this.instance = null;
    this.db = null;
  }

  /**
   * 创建BookshelfDB对象
   * @returns {BookshelfDB} BookshelfDB实例
   */
  static getInstance() {
    if (!this.instance) {
      this.instance = new BookshelfDB();
    }
    return this.instance;
  }

  /**
   * 连接数据库
   * @returns {Promise}
   */
  connect() {
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbFile, (err) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve("connect ok.");
        }
      });
    });
  }
  /**
   * 关闭数据库
   * @returns {Promise}
   */
  close() {
    return new Promise((resolve, reject) => {
      this.db.close((err) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve("close ok.");
        }
      });
    });
  }

  /**
   * 获取指定ID的书架信息
   * @param {Number} bookshelfId 书架id
   * @param {Number} userId 用户id
   * @returns {Promise}
   */
  find(bookshelfId, userId) {
    return new Promise((resolve, reject) => {
      let sql = `SELECT 
           a.id AS id,
           a.book_id AS book_id,
           a.user_id AS user_id,
           a.read_status AS read_status,
           a.ranking AS ranking,
           a.created_time AS created_time,
           a.updated_time AS updated_time,
           b.title AS title,
           b.pic AS pic,
           b.local_pic AS local_pic,
           b.author AS author,
           b.publisher AS publisher, 
           b.producer AS producer,
           b.subtitle AS subtitle, 
           b.originalTitle AS originalTitle, 
           b.translator AS translator, 
           b.pubdate AS pubdate, 
           b.pages AS pages, 
           b.price AS price, 
           b.binding AS binding, 
           b.series AS series, 
           b.isbn AS isbn, 
           b.intro AS intro, 
           b.doubanId AS doubanId 
          FROM bookshelves AS a 
          LEFT JOIN books AS b
          ON a.book_id = b.id 
          WHERE a.id = ? AND a.user_id = ?
        `;
      let params = [bookshelfId, userId];
      this.db.get(sql, params, (err, result) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  /**
   * 获取指定用户的书架信息列表
   * @param {Number} limit 数量
   * @param {Number} offset 开始
   * @returns {Promise}
   */
  findAll(userId, orderBy = "id", sort = "desc", limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
      let sql = `SELECT 
          a.id AS id,
          a.book_id AS book_id,
          a.user_id AS user_id,
          a.read_status AS read_status,
          a.ranking AS ranking,
          a.created_time AS created_time,
          a.updated_time AS updated_time,
          b.title AS title,
          b.pic AS pic,
          b.local_pic AS local_pic,
          b.author AS author,
          b.publisher AS publisher, 
          b.producer AS producer,
          b.subtitle AS subtitle, 
          b.originalTitle AS originalTitle, 
          b.translator AS translator, 
          b.pubdate AS pubdate, 
          b.pages AS pages, 
          b.price AS price, 
          b.binding AS binding, 
          b.series AS series, 
          b.isbn AS isbn, 
          b.intro AS intro, 
          b.doubanId AS doubanId 
          FROM bookshelves AS a 
          LEFT JOIN books AS b
          ON a.book_id = b.id 
          WHERE a.user_id = ?
        `;
      let params = [userId];
      if (limit === -1) {
        sql += ` ORDER BY ${orderBy} ${sort}`;
      } else if (offset === -1) {
        sql += ` ORDER BY ${orderBy} ${sort} LIMIT ?`;
        params.push(limit);
      } else {
        sql += ` ORDER BY ${orderBy} ${sort} LIMIT ? OFFSET ?`;
        params.push(limit);
        params.push(offset);
      }
      this.db.all(sql, params, (err, result) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  /**
   * 新增
   * @param {Object} bookshelf 书架数据
   * @returns {Promise}
   */
  add(bookshelf) {
    return new Promise((resolve, reject) => {
      let sql = `INSERT INTO bookshelves (
    user_id, book_id, created_time, updated_time
) VALUES (?, ?, ?, ?);`;
      let params = [
        bookshelf.userId,
        bookshelf.bookId,
        bookshelf.createdTime,
        bookshelf.updatedTime,
      ];
      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.lastID); //插入的数据的自增ID
        }
      });
    });
  }

  /**
   * 修改
   * @param {Object} Bookshelf 书架数据
   * @returns {Promise}
   */
  update(bookshelf) {
    return new Promise((resolve, reject) => {
      let sql = `UPDATE bookshelves SET 
 read_status = ?, ranking = ?, updated_time = ?
 WHERE id = ?;`;
      let params = [
        bookshelf.readStatus,
        bookshelf.ranking,
        bookshelf.updatedTime,
        bookshelf.id,
      ];

      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.changes); //影响的记录数
        }
      });
    });
  }

  /**
   * 删除
   * @param {Number} bookshelfId 用户ID
   * @returns {Promise}
   */
  remove(bookshelfId) {
    return new Promise((resolve, reject) => {
      let sql = "DELETE FROM bookshelves WHERE id = ?";
      let params = [bookshelfId];
      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.changes); //影响的记录数
        }
      });
    });
  }

  /**
   * 获取指定用户书架数量
   * @returns {Promise}
   */
  getCount(userId) {
    return new Promise((resolve, reject) => {
      let sql = "SELECT count(1) AS total FROM bookshelves WHERE user_id = ?";
      let params = [userId];
      this.db.get(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  /**
   * 更新书架中书籍状态
   * 0 - 未读
   * 1 - 想读
   * 2 - 正在读
   * 3 - 已读完
   * @param {Number} id ID
   * @param {Number} readStatus
   * @returns {Promise}
   */
  updateReadStatus(id, readStatus) {
    return new Promise((resolve, reject) => {
      let sql = `UPDATE bookshelves SET read_status = ? WHERE id = ?;`;
      let params = [readStatus, id];

      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.changes); //影响的记录数
        }
      });
    });
  }

  /**
   * 更新书架中书籍评分
   * 1-5星（1-10）
   * @param {Number} id ID
   * @param {Number} ranking
   * @returns {Promise}
   */
  updateRanking(id, ranking) {
    return new Promise((resolve, reject) => {
      let sql = `UPDATE bookshelves SET ranking = ? WHERE id = ?;`;
      let params = [ranking, id];

      this.db.run(sql, params, function (err, result) {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(this.changes); //影响的记录数
        }
      });
    });
  }

  /**
   * 获取指定ID的书架信息
   * @param {Number} bookshelfId 书架id
   * @param {Number} userId 用户id
   * @returns {Promise}
   */
  findByBookId(bookId, userId) {
    return new Promise((resolve, reject) => {
      let sql = `SELECT 
             a.id AS id,
             a.book_id AS book_id,
             a.user_id AS user_id,
             a.read_status AS read_status,
             a.ranking AS ranking,
             a.created_time AS created_time,
             a.updated_time AS updated_time,
             b.title AS title,
             b.pic AS pic,
             b.local_pic AS local_pic,
             b.author AS author,
             b.publisher AS publisher, 
             b.producer AS producer,
             b.subtitle AS subtitle, 
             b.originalTitle AS originalTitle, 
             b.translator AS translator, 
             b.pubdate AS pubdate, 
             b.pages AS pages, 
             b.price AS price, 
             b.binding AS binding, 
             b.series AS series, 
             b.isbn AS isbn, 
             b.intro AS intro, 
             b.doubanId AS doubanId 
            FROM bookshelves AS a 
            LEFT JOIN books AS b
            ON a.book_id = b.id 
            WHERE a.book_id = ? AND a.user_id = ?
          `;
      let params = [bookId, userId];
      this.db.get(sql, params, (err, result) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }

  /**
   * 获取指定书籍ID的综合评分
   * @param {Number} bookId 书籍id
   * @returns  {Promise}
   */
  avgRankingByBookId(bookId) {
    return new Promise((resolve, reject) => {
      let sql = `SELECT AVG(ranking) AS average FROM bookshelves WHERE book_id = ?`;
      let params = [bookId];
      this.db.get(sql, params, (err, result) => {
        if (err) {
          util.err(err);
          reject(err);
        } else {
          resolve(result);
        }
      });
    });
  }
}

module.exports = BookshelfDB;